﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.SqlClient;
using Dapper;
using System.Collections;

namespace DapperEx
{
    public abstract class SqlQuery
    {
        private static object objLock = new object();
        protected QueryOrder _Order;  //排序
        protected StringBuilder _Sql; //组装的SQL WHERE部分
        protected string _WhereSql;
        protected IList<DbParameterModel> _Param;  //参数动态类
        protected string ParamPrefix = "@"; //参数前缀
        internal ModelDes _ModelDes;//处理的实体对象描述
        protected int _PageIndex;
        protected int _PageSize;
        protected DBType DbType;
        //动态参数类缓存
        protected static Dictionary<string, Type> DynamicParamModelCache = new Dictionary<string, Type>();

        /// <summary>
        /// 查询TOP
        /// </summary>
        public virtual int TopNum { get; set; }
        /// <summary>
        /// 排序
        /// </summary>
        public virtual QueryOrder Order { get { return _Order; } }
        /// <summary>
        /// SQL字符串,只表示包括Where部分
        /// </summary>
        internal virtual string WhereSql
        {
            get
            {
                if (string.IsNullOrEmpty(_WhereSql))
                {
                    var sb = new StringBuilder();
                    var arr = _Sql.ToString().Split(' ').Where(m => !string.IsNullOrEmpty(m)).ToList();
                    for (int i = 0; i < arr.Count; i++)
                    {
                        if (i == 0 && (arr[i] == "AND" || arr[i] == "OR"))
                        {
                            continue;
                        }
                        if (i > 0 && arr[i - 1] == "(" && (arr[i] == "AND" || arr[i] == "OR"))
                        {
                            continue;
                        }
                        sb.Append(" ");
                        sb.Append(arr[i]);
                    }
                    return sb.ToString();
                }
                else
                {
                    return _WhereSql;
                }
            }
            set
            {
                _WhereSql = value;
            }
        }
        /// <summary>
        /// 排序
        /// </summary>
        internal virtual string OrderSql
        {
            get
            {
                var sb = new StringBuilder();
                if (Order != null)
                {
                    sb.Append(" ");
                    sb.Append("ORDER BY");
                    sb.Append(" ");
                    sb.Append(Order.Field);
                    sb.Append(" ");
                    var desc = Order.IsDesc ? "DESC" : "ASC";
                    sb.Append(desc);
                }
                return sb.ToString();
            }
        }
        /// <summary>
        /// 查询参数对象
        /// </summary>
        internal object Param
        {
            get
            {
                if (this._Param != null && this._Param.Count > 0)
                {
                    #region 处理参数对象是否存在缓存中
                    var paramKeys = this._Param.Select(info => info.ParameterName).ToList();//当前使用的参数集合
                    var listCacheKeys = new List<string>();
                    listCacheKeys.Add(this._ModelDes.TableName);
                    listCacheKeys.AddRange(paramKeys);

                    var cacheKey = string.Empty;
                    foreach (var key in DynamicParamModelCache.Keys.Where(m => m.StartsWith(this._ModelDes.TableName)))
                    {
                        if (listCacheKeys.All(m => key.Split('_').Contains(m)))//当前参数是否是一样已经缓存的子集
                        {
                            cacheKey = key;
                            break;
                        }
                    }
                    if (string.IsNullOrEmpty(cacheKey))//为空则说明缓存不存在相应数据类型
                    {
                        cacheKey = string.Join("_", listCacheKeys);
                    }
                    #endregion
                    Type modelType;
                    lock (objLock)//防止多线程同时操作DynamicParamModelCache
                    {
                        DynamicParamModelCache.TryGetValue(cacheKey, out modelType);
                        if (modelType == null)
                        {
                            var tyName = "CustomDynamicParamClass";
                            modelType = CustomDynamicBuilder.DynamicCreateType(tyName, this._Param);
                            DynamicParamModelCache.Add(cacheKey, modelType);
                        }
                    }
                    var model = Activator.CreateInstance(modelType);
                    string value;
                    foreach (var item in this._Param)
                    {
                        modelType.GetProperty(item.ParameterName).SetValue(model, item.Value, null);
                    }

                    return model;
                }
                else
                {
                    return null;
                }
            }
        }
        /// <summary>
        /// 插入语句SQL
        /// </summary>
        internal virtual string InsertSql
        {
            get
            {
                StringBuilder keys = new StringBuilder();
                StringBuilder values = new StringBuilder();

                List<string> columnList = Common.GetExecColumns(this._ModelDes).Select(info => info.FieldName).ToList();
                columnList.ForEach((filedName) =>
                {
                    keys.AppendFormat("{0},", filedName);
                    values.AppendFormat("@{0},", filedName);
                });
                return string.Format("insert into {0}({1}) values({2});", this._ModelDes.TableName, keys.ToString(0, keys.Length - 1), values.ToString(0, values.Length - 1));
            }
        }

        /// <summary>
        /// 插入语句SQL
        /// </summary>
        internal virtual string IdentitySql
        {
            get
            {
                return "select @@identity";
            }
        }

        /// <summary>
        /// 删除SQL
        /// </summary>
        internal virtual string DeleteSql
        {
            get
            {
                return string.Format("DELETE FROM {0} where 1=1 {1}", this._ModelDes.TableName, this.WhereSql);
            }
        }
        /// <summary>
        /// 修改SQL
        /// </summary>
        internal virtual string UpdateSql
        {
            get
            {
                if (string.IsNullOrEmpty(WhereSql))//没有where条件的情况
                {
                    var p = Common.GetPrimary(this._ModelDes);
                    WhereSql = string.Format(" and {0}={1}", p.Column, ParamPrefix + p.Field);
                }

                StringBuilder keys = new StringBuilder();
                List<string> columnList = Common.GetExecColumns(this._ModelDes).Select(info => info.FieldName).ToList();
                this._Param.Where(info => columnList.Contains(info.ParameterName)).Select(info => info.ParameterName).ToList().ForEach((filedName) =>
                {
                    keys.AppendFormat(" {0}={1}{2},", filedName, ParamPrefix, filedName);
                });
                return string.Format("update {0} set {1} where 1=1 {2}", this._ModelDes.TableName, keys.ToString(0, keys.Length - 1), WhereSql);
            }
        }
        /// <summary>
        /// 查询SQL
        /// </summary>
        internal virtual string QuerySql
        {
            get
            {
                var sqlStr = "";
                if (TopNum > 0)
                {
                    switch (this.DbType)
                    {
                        case DBType.SqlServer:
                        case DBType.SqlServerCE:

                            sqlStr = string.Format("SELECT TOP {0} *  FROM {1} where 1=1 {2} {3}", this.TopNum, this._ModelDes.TableName, this.WhereSql, this.OrderSql);
                            break;
                        case DBType.Oracle:

                            var strWhere = "";
                            if (string.IsNullOrEmpty(this.WhereSql))
                            {
                                strWhere = string.Format(" and  ROWNUM <= {0} ", this.TopNum);
                            }
                            else
                            {
                                strWhere = string.Format(" {0} AND ROWNUM <= {1} ", this.WhereSql, this.TopNum);
                            }
                            sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2}", this._ModelDes.TableName, strWhere, this.OrderSql);
                            break;
                        default:

                            sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2} LIMIT {3}", this._ModelDes.TableName, this.WhereSql, this.OrderSql, this.TopNum);
                            break;
                    }
                }
                else
                {
                    sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2}", this._ModelDes.TableName, this.WhereSql, this.OrderSql);
                }
                return sqlStr;
            }
        }
        /// <summary>
        /// 分页SQL
        /// </summary>
        internal virtual string PageSql
        {
            get
            {
                var sqlPage = "";
                var orderStr = string.IsNullOrEmpty(this.OrderSql) ? "ORDER BY " + this._ModelDes.Properties.FirstOrDefault().Column : this.OrderSql;

                if (this.DbType == DBType.SqlServer)
                {
                    sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) rid, * FROM {4} where 1=1 {1} ) p_paged WHERE rid>{2} AND rid<={3}",
                                             orderStr, this.WhereSql, (this._PageIndex - 1) * this._PageSize, (this._PageIndex - 1) * this._PageSize + this._PageSize, this._ModelDes.TableName);
                }
                else if (this.DbType == DBType.Oracle)
                {
                    var tP = this.DbType == DBType.Oracle ? this._ModelDes.TableName + ".*" : "*";
                    sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) rid, .* FROM {4} where 1=1 {1} ) p_paged WHERE rid>{2} AND rid<={3}",
                                             orderStr, this.WhereSql, (this._PageIndex - 1) * this._PageSize, (this._PageIndex - 1) * this._PageSize + this._PageSize, this._ModelDes.TableName);
                }
                else if (this.DbType == DBType.SqlServerCE)
                {
                    sqlPage = string.Format("SELECT * FROM {0} where 1=1 {1} {2} OFFSET {3} ROWS FETCH NEXT {4} ROWS ONLY",
                        this._ModelDes.TableName, this.WhereSql, orderStr, (this._PageIndex - 1) * this._PageSize, (this._PageIndex - 1) * this._PageSize + this._PageSize);
                }
                else
                {
                    sqlPage = string.Format("SELECT * FROM {0} where 1=1 {1} {2} LIMIT {1} OFFSET {2}",
                        this._ModelDes.TableName, this.WhereSql, orderStr, (this._PageIndex - 1) * this._PageSize, (this._PageIndex - 1) * this._PageSize + this._PageSize);
                }
                return sqlPage;
            }
        }

        internal string BuildPageSql(string tableName, int currentPageIndex, int pageSize, string idFieldName, SortOrder sortOrder = SortOrder.Ascending)
        {
            string sqlStr = string.Empty;

            idFieldName = string.IsNullOrEmpty(idFieldName) ? Common.GetPrimary(_ModelDes).Field : idFieldName;
            string orderSql = string.Format(" order  by {0} {1}", idFieldName, sortOrder == SortOrder.Descending ? "desc" : "asc");

            if (this.DbType == DBType.SqlServer)
            {
                string whereCase = string.Empty;
                if (currentPageIndex == 1)
                {
                    whereCase = string.Format("{0} {1} ", this.WhereSql, orderSql);
                }
                else
                {
                    string strTemp = sortOrder == SortOrder.Descending ? " >(select max " : " <(select min ";
                    whereCase = string.Format("{0} and {1} {2}({1}) from (select top {4} {1} from {3} where 1=1 {0} {5}) as t) {5} ", this.WhereSql, idFieldName, strTemp, tableName, (currentPageIndex - 1) * pageSize, orderSql);
                }
                sqlStr = string.Format(@"select  top {0} * from {1} WHERE  1=1  {2} ", pageSize, tableName, whereCase);
            }
            else if (this.DbType == DBType.Oracle)
            {
                sqlStr = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) rid, .* FROM {4} {1} ) p_paged WHERE rid>{2} AND rid<={3}",
                     orderSql, this.WhereSql, (currentPageIndex - 1) * pageSize, (currentPageIndex - 1) * pageSize + pageSize, this._ModelDes.TableName);
            }
            else if (this.DbType == DBType.SqlServerCE)
            {
                sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2} OFFSET {3} ROWS FETCH NEXT {4} ROWS ONLY",
                    this._ModelDes.TableName, this.WhereSql, orderSql, (currentPageIndex - 1) * pageSize, (currentPageIndex - 1) * pageSize + pageSize);
            }
            else
            {
                sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2} LIMIT {1} OFFSET {2}",
                    this._ModelDes.TableName, this.WhereSql, orderSql, (currentPageIndex - 1) * pageSize, (currentPageIndex - 1) * pageSize + pageSize);
            }
            return sqlStr;
        }
        /// <summary>
        /// 数据总是SQL
        /// </summary>
        internal virtual string CountSql
        {
            get
            {
                return string.Format("SELECT COUNT(*) DataCount FROM {0} where 1=1 {1}", this._ModelDes.TableName, this.WhereSql);
            }
        }



        protected SqlQuery()
        {
            this._Sql = new StringBuilder();
        }
        /// <summary>
        /// TOP
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        public SqlQuery Top(int top)
        {
            this.TopNum = top;
            return this;
        }
        /// <summary>
        /// 将其它参数添加到参数对象中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        internal SqlQuery AppendParam<T>(T t, dynamic param = null) where T : class
        {
            if (this._Param == null)
            {
                this._Param = new List<DbParameterModel>();
            }
            if (t != null)
            {
                //添加实体类参数
                var model = Common.GetModelDes<T>();
                foreach (var item in model.Properties)
                {
                    var value = model.ClassType.GetProperty(item.Field).GetValue(t, null);
                    if (value == null)
                    {
                        continue;
                    }
                    var pmodel = new DbParameterModel();
                    pmodel.ParameterName = item.Field;
                    pmodel.Value = value;
                    if (value != null)
                        pmodel.ParameterType = value.GetType();
                    else
                        pmodel.ParameterType = typeof(System.String);
                    this._Param.Add(pmodel);
                }
            }
            //添加自定义参数
            if (param != null)
            {
                Action<dynamic> singleParamAdd = (singleParam) =>
                {
                    Type type = ((object)singleParam).GetType();
                    DbParameterModel pmodel = null;
                    foreach (var pro in type.GetProperties())
                    {
                        var value = pro.GetValue(singleParam, null);
                        if (value == null)
                        {
                            continue;
                        }
                        pmodel = new DbParameterModel();
                        pmodel.ParameterName = pro.Name;
                        pmodel.Value = value;
                        if (value != null)
                            pmodel.ParameterType = value.GetType();
                        else
                            pmodel.ParameterType = typeof(System.String);
                        this._Param.Add(pmodel);
                    }
                };

                IEnumerable multiExec = (object)param as IEnumerable;
                if (multiExec != null && !(multiExec is string))
                {
                    foreach (var obj in multiExec)
                    {
                        singleParamAdd(obj);
                    }
                }
                if ((object)param != null)
                {
                    singleParamAdd(param);
                }
                //paramsList.ForEach((dpModel) =>
                //{
                //    if (dpModel.ParameterType == null)
                //    {
                //        dpModel.ParameterType = typeof(System.String);
                //    }
                //    this._Param.Add(dpModel);
                //});
            }
            return this;
        }
        /// <summary>
        /// 分页条件
        /// </summary>
        /// <param name="pindex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public SqlQuery Page(int pindex, int pageSize)
        {
            this._PageIndex = pindex;
            this._PageSize = pageSize;
            return this;
        }
    }
    /// <summary>
    ///  组装查询
    /// </summary>
    public class SqlQuery<T> : SqlQuery where T : class
    {
        private SqlQuery()
            : base()
        {
            this._ModelDes = Common.GetModelDes<T>();
        }
        /// <summary>
        /// 创建
        /// </summary>
        /// <returns></returns>
        public static SqlQuery<T> Builder(DbBase db)
        {
            var result = new SqlQuery<T>();
            result.ParamPrefix = db.ParamPrefix;
            result.DbType = db.DbType;
            return result;
        }
        /// <summary>
        /// 创建排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expr"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public SqlQuery<T> OrderBy(Expression<Func<T, object>> expr, bool desc)
        {
            var field = Common.GetPropertyByExpress<T>(this._ModelDes, expr).Column;
            this._Order = new QueryOrder() { Field = field, IsDesc = desc };
            return this;
        }
        /// <summary>
        /// TOP
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        public new SqlQuery<T> Top(int top)
        {
            this.TopNum = top;
            return this;
        }
        /// <summary>
        /// 左括号(
        /// </summary>
        /// <param name="isAnd">true为AND false为OR</param>
        /// <returns></returns>
        public SqlQuery<T> LeftInclude(bool isAnd = true)
        {
            var cn = isAnd ? "AND" : "OR";
            this._Sql.Append(" ");
            this._Sql.Append(cn);
            this._Sql.Append(" ");
            this._Sql.Append("(");
            return this;
        }
        /// <summary>
        /// 右括号)
        /// </summary>
        /// <returns></returns>
        public SqlQuery<T> RightInclude()
        {
            this._Sql.Append(" ");
            this._Sql.Append(")");
            return this;
        }
        /// <summary>
        /// AND方式连接一条查询条件
        /// </summary>
        /// <param name="expr"></param>
        /// <param name="operation"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlQuery<T> AndWhere(Expression<Func<T, object>> expr, OperationMethod operation, object value)
        {
            return Where(expr, operation, value, true);
        }
        /// <summary>
        ///  Or方式连接一条查询条件
        /// </summary>
        /// <param name="expr"></param>
        /// <param name="operation"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlQuery<T> OrWhere(Expression<Func<T, object>> expr, OperationMethod operation, object value)
        {
            return Where(expr, operation, value, false);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="expr"></param>
        /// <param name="operation"></param>
        /// <param name="value"></param>
        /// <param name="isAnd">true为AND false为OR</param>
        /// <returns></returns>
        private SqlQuery<T> Where(Expression<Func<T, object>> expr, OperationMethod operation, object value, bool isAnd)
        {
            var cn = isAnd ? "AND" : "OR";
            var field = Common.GetPropertyByExpress<T>(this._ModelDes, expr).Column;
            var op = GetOpStr(operation);
            StringBuilder sb = new StringBuilder();
            this._Sql.Append(" ");
            this._Sql.Append(cn);
            this._Sql.Append(" ");
            this._Sql.Append(field);
            this._Sql.Append(" ");
            this._Sql.Append(op);
            this._Sql.Append(" ");
            var model = AddParam(operation, field, value);
            this._Sql.Append(this.ParamPrefix + model.ParameterName);

            return this;
        }
        /// <summary>
        /// 比较符
        /// </summary>
        /// <param name="method"></param>
        /// <returns></returns>
        private string GetOpStr(OperationMethod method)
        {
            switch (method)
            {
                case OperationMethod.Contains:
                    return "LIKE";
                case OperationMethod.EndsWith:
                    return "LIKE";
                case OperationMethod.Equal:
                    return "=";
                case OperationMethod.Greater:
                    return ">";
                case OperationMethod.GreaterOrEqual:
                    return ">=";
                case OperationMethod.In:
                    return "IN";
                case OperationMethod.Less:
                    return "<";
                case OperationMethod.LessOrEqual:
                    return "<=";
                case OperationMethod.NotEqual:
                    return "<>";
                case OperationMethod.StartsWith:
                    return "LIKE";
            }
            return "=";
        }
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="operation"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private object CreateParam(OperationMethod method, object value)
        {
            switch (method)
            {
                case OperationMethod.Contains:
                    return string.Format("%{0}%", value);
                case OperationMethod.EndsWith:
                    return string.Format("%{0}", value);
                case OperationMethod.Equal:
                    return value;
                case OperationMethod.Greater:
                    return value;
                case OperationMethod.GreaterOrEqual:
                    return value;
                case OperationMethod.In:
                    return value;
                case OperationMethod.Less:
                    return value;
                case OperationMethod.LessOrEqual:
                    return value;
                case OperationMethod.NotEqual:
                    return value;
                case OperationMethod.StartsWith:
                    return string.Format("{0}%", value);
            }
            return value;
        }
        /// <summary>
        /// 通过方法和值创建一个参数对象并记录
        /// </summary>
        /// <param name="method"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private DbParameterModel AddParam(OperationMethod method, string field, object value)
        {
            if (this._Param == null)
            {
                this._Param = new List<DbParameterModel>();
            }

            switch (method)
            {
                #region
                case OperationMethod.Contains:
                    value = string.Format("%{0}%", value);
                    break;
                case OperationMethod.StartsWith:
                    value = string.Format("%{0}%", value);
                    break;
                case OperationMethod.EndsWith:
                    value = string.Format("%{0}%", value);
                    break;
                case OperationMethod.Equal:
                case OperationMethod.Greater:
                case OperationMethod.GreaterOrEqual:
                case OperationMethod.In:
                case OperationMethod.Less:
                case OperationMethod.LessOrEqual:
                case OperationMethod.NotEqual:
                    break;
                #endregion
            }

            var model = new DbParameterModel();
            model.ParameterName = field + GetParamIndex(field);
            model.ParameterType = value.GetType();
            model.Value = value;
            this._Param.Add(model);

            return model;
        }
        private string GetParamIndex(string field)
        {
            var key = this._Param.Where(info => info.ParameterName.StartsWith(field)).FirstOrDefault();
            if (key == null)
            {
                return "1";
            }
            else
            {
                return (int.Parse(key.ParameterName.Remove(0, field.Length)) + 1).ToString();
            }
        }
    }
}
